CREATE OR REPLACE PROCEDURE AUTOSC.get_new_onwatch_task
(
	p_onwatchTask OUT SYS_REFCURSOR,
	p_systemId	IN VARCHAR2,
	p_codeId	IN NUMBER,
	p_status	IN VARCHAR2
)
IS
	v_task_list_id	LONG;
	v_sql	LONG;
	v_markTask LONG;
	ERR_MSG     VARCHAR2(255);
    ERR_CDE     NUMBER;
BEGIN
	select AUTOSC.act_onwatch_task_list_id_seq.nextVal into v_task_list_id from dual;
	
	v_markTask := 'update (SELECT onwatchTask.onwatch_task_list_id from ACT_ONWATCH_TASK onwatchTask' ||
				' JOIN ACT_TASK task ON onwatchTask.TASK_ID = task.TASK_ID' ||
				' JOIN ACT_REQUEST request ON task.request_id = request.request_id' ||
				' WHERE request.system_id = ''' || p_systemId || '''' ||
				' AND task.code_id = ' || p_codeId || 
				' AND onwatchTask.status = ''' || p_status || '''' ||
				' AND onwatchTask.onwatch_task_list_id = 0' ||
				') set onwatch_task_list_id = ' || v_task_list_id;
	EXECUTE IMMEDIATE v_markTask;
	v_sql := 'select onwatch_task_id, onwatch_task_list_id, entered_date, execution_time_in_sec, ' ||
	 		'delivered_date, completion_date, status, task_order, task_id ' ||
	 		'from act_onwatch_task where onwatch_task_list_id=' || v_task_list_id;	
	 		
	OPEN p_onwatchTask FOR v_sql;
END get_new_onwatch_task;
/